﻿package k.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Map;
import java.util.Iterator;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.math.BigDecimal;
import java.sql.DatabaseMetaData;

import k.server.LogConfig;
import k.util.StringUtil;

/**
 * 连接数据库的工具类,被定义成不可继承且是私有访问
 */
public final class DBUtils {

	private Connection conn;

	public DBUtils() {

	}

	/**
	 * 获取数据库的连接
	 * 
	 * @return conn
	 */
	public Connection getConnection(String url, String user, String psw)
			throws Exception {
		if (null == conn) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(url, user, psw);
			} catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		return conn;
	}

	/**
	 * 释放资源
	 * 
	 * @param conn
	 * @param pstmt
	 * @param rs
	 */
	public void closeResources(Connection conn, Statement pstmt,
			ResultSet rs) throws Exception {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (null != pstmt) {
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					} finally {
						if (null != conn) {
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
								throw new RuntimeException(e);
							}
						}
					}
				}
			}
		}
	}

	public void closeConnection() {
		if (conn == null)
			return;
		try {
			if (!conn.isClosed()) {
				// 关闭数据库连接
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}

	}

	/**
	 * 在一个数据库连接上执行一个静态SQL语句查询
	 * 
	 * @param conn
	 *            数据库连接
	 * @param staticSql
	 *            静态SQL语句字符串
	 * @return 返回查询结果集ResultSet对象
	 */
	public ResultSet executeQuery(String staticSql) throws Exception {
		ResultSet rs = null;
		try {
			// 创建执行SQL的对象
			Statement stmt = conn.createStatement();
			// 执行SQL，并获取返回结果
			rs = stmt.executeQuery(staticSql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return rs;
	}
	
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public   void batchInsert( LogConfig lc,Map<Integer, Map<Integer, String>> map)
    throws Exception {

		Date date1 = new Date();
		Date date2 = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd-HH:mm:ss.SSS");
		executeQuery("set names 'utf8'");
		// executeQuery("set character_set_client='utf8'");

		conn.setAutoCommit(false);

		PreparedStatement prest = conn.prepareStatement(lc.getSql(),
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

		for (Iterator iterator = map.entrySet().iterator(); iterator.hasNext();) {
			Map.Entry entry = (Map.Entry) iterator.next();
			// 先遍历一维map
			// 日期
			prest.setDate(1, new java.sql.Date(date1.getTime()));
			Map<Integer, String> map1 = (Map<Integer, String>) entry.getValue();
			if (entry.getValue() instanceof Map) {
				int i = 0;
				for (Iterator it = map1.entrySet().iterator(); it.hasNext();) {
					Map.Entry entry2 = (Map.Entry) it.next();
					// 再遍历二维map
					String value = String.valueOf(entry2.getValue()).trim();
					if (lc.getTypes().get(i).equals("date")) {
						date2 = sdf.parse(value);
						prest.setDate(++i,
								new java.sql.Date(new Date().getTime()));
					} else if (lc.getTypes().get(i).equals("time")) {
						date2 = sdf.parse(value);
						prest.setTimestamp(++i,
								new java.sql.Timestamp(date2.getTime()));
					} else if (lc.getTypes().get(i).equals("int")) {
						if (!StringUtil.isinteger(value)) value = "0";
						BigDecimal bd = new BigDecimal(value);
						prest.setBigDecimal(++i, bd);
					} else if (lc.getTypes().get(i).equals("double")) {
						if (!StringUtil.isnumber(value)) value = "0.0";
						double d = Double.parseDouble(value);
						prest.setDouble(++i, d);
					} else {
						prest.setString(++i, value);
					}
				}
				prest.addBatch();
			}
		}
		prest.executeBatch();
		conn.commit();
		prest.clearBatch();
	}

	public int executeDmlAndDdl(String sql) throws Exception {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public boolean CheckTableExit(String sql) throws Exception {
		String stringarray[] = null;
		String stringarray2[] = null;
		String strTableName = null;

		DatabaseMetaData meta = conn.getMetaData();
		// 获取表名是否存在，不存在则创建
		stringarray = sql.split(" ");
		strTableName = stringarray[2];
		if (stringarray[2].contains("(")) {
			stringarray2 = stringarray[2].split("\\(");
			strTableName = stringarray2[0];
		}
		ResultSet rsTables = meta.getTables(conn.getCatalog(), null,
				strTableName, new String[] { "TABLE" });
		while (rsTables.next()) {
			rsTables.close();
			return true;
		}
		rsTables.close();
		return false;

	}
	
	public int delTableData(String sql, String date) throws Exception {
		Statement stmt = null;
		String stringarray[] = null;
		String stringarray2[] = null;
		String strTableName = null;
		String delSql = null;
		try {
			// 获取表名是否存在，不存在则创建
			stringarray = sql.split(" ");
			strTableName = stringarray[2];
			if (stringarray[2].contains("(")) {
				stringarray2 = stringarray[2].split("\\(");
				strTableName = stringarray2[0];
			}
			if(date != null){
				delSql = "delete from " + strTableName + " where date = " + "'" + date + "'";
			}

			stmt = conn.createStatement();
			return stmt.executeUpdate(delSql);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

}
